package com.xyh.dao.impl;

import com.xyh.bean.Area;
import com.xyh.bean.Insect;
import com.xyh.bean.ManagerClass;
import com.xyh.dao.IClassDao;
import com.xyh.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: XiongYuhao
 * @Date: 2020/10/25 0025-下午 11:48
 * @Description: MyForestManagerSystem
 * @version：1.0
 */
public class ClassDaoImpl implements IClassDao {

    public int getId(String className) {
        int id = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            String sql = "select pk_id from t_class where f_className = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,className);
            rs = ps.executeQuery();

            if (rs.next()){
                id = rs.getInt("pk_id");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return id;
    }

    @Override
    public int classCounts(String className, String managerArea) {
        int counts = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            String sql = "select count(0) from t_class inner join t_area on t_class.pk_id = t_area.pk_id where 1=1 ";
            if (className != null && !className.equalsIgnoreCase("")) {
                sql += " and f_className like '%" + className + "%'";
            }
            if (managerArea != null && !managerArea.equalsIgnoreCase("")) {
                sql += " and f_areaName like '%" + managerArea + "%'";
            }
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                counts = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return counts;
    }

    @Override
    public List<ManagerClass> classListByPage(int pageNo, int pageSize, String className, String managerArea) {
        List<ManagerClass> mcs = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            StringBuffer sql = new StringBuffer("select t_class.pk_id,f_className,f_managerPeople,f_tel,f_number,f_createTime,t_area.pk_id,f_areaName,f_treeType,f_land,f_advTree from t_class inner join t_area on t_class.pk_id = t_area.pk_id where 1=1 ");
            if (className != null && !className.equalsIgnoreCase("")) {
                sql.append(" and f_className like '%" + className + "%'");
            }
            if (managerArea != null && !managerArea.equalsIgnoreCase("")) {
                sql.append(" and f_areaName like '%" + managerArea + "%'");
            }
            sql.append(" limit " + (pageNo - 1) * pageSize + "," + pageSize);

            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                ManagerClass mc = new ManagerClass();
                mc.setClassId(rs.getInt("t_class.pk_id"));
                mc.setClassName(rs.getString("f_className"));
                mc.setManagerPeople(rs.getString("f_managerPeople"));
                mc.setTel(rs.getString("f_tel"));
                mc.setNum(rs.getInt("f_number"));
                mc.setCreateTime(rs.getString("f_createTime"));

                Area area = new Area();
                area.setAreaId(rs.getInt("t_area.pk_id"));
                area.setAreaName(rs.getString("f_areaName"));
                area.setTreeType(rs.getString("f_treeType"));
                area.setLand(rs.getString("f_land"));
                area.setAdvTree(rs.getString("f_advTree"));

                mc.setArea(area);
                mcs.add(mc);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return mcs;
    }

    @Override
    public int updateClass(ManagerClass mc) {
        int line = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            String sql = "update t_class set f_managerPeople=?,f_tel=? where pk_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,mc.getManagerPeople());
            ps.setString(2,mc.getTel());
            ps.setInt(3,mc.getClassId());
            line = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,ps,conn);
        }
        return line;
    }

    @Override
    public int addClass(ManagerClass mc) {
        int line = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            String sql = "insert into t_class values (?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,mc.getArea().getAreaId());
            ps.setString(2,mc.getClassName());
            ps.setString(3,mc.getManagerPeople());
            ps.setString(4,mc.getTel());
            ps.setInt(5,mc.getNum());
            ps.setString(6,mc.getCreateTime());
            line = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,ps,conn);
        }
        return line;
    }
}
